<?php 
/* 	These code was generated using phpCIGen v 2.a (21/05/2012)
	#zaqi 		zaqi.smart@gmail.com,http://zenzaqi.blogspot.com,
    #CV. Trust Solution, jl. Saronojiwo 2/19 Surabaya, http://www.ts.co.id

	+ Module  		: t_negosiasi Model
	+ Description	: For record model process back-end
	+ Filename 		: c_t_negosiasi.php
 	+ creator 		: 
 	+ Created on 24/Jul/2012 10:52:52

*/

class M_t_negosiasi extends CI_Model{

		//constructor
		function __construct() {
			parent::__construct();
		}
		
		function negosiasidet_list($task,$negosiasi_id,$branch,$project,$start,$end) {
			if($task=='detail'){
				$query = "SELECT negosiasidet_id, jobdet_id, jobdet_name, negosiasidet_volume, negosiasidet_price
						FROM t_negosiasidet JOIN m_jobdet ON(t_negosiasidet.m_jobdet_id = m_jobdet.jobdet_id)
						WHERE t_negosiasi_id=".$negosiasi_id;
			}else{
				$query = "SELECT jobdet_id, jobdet_name, rapkdet_volume AS negosiasidet_volume, rapkdet_price AS negosiasidet_price
						FROM t_rapk JOIN t_rapkdet ON(t_rapkdet.t_rapk_id = t_rapk.rapk_id)
						JOIN m_jobdet ON(m_jobdet.jobdet_id = t_rapkdet.m_jobdet_id)
						WHERE t_rapk.m_branch_id=".$branch." AND t_rapk.t_project_id=".$project;
			}
			
			$result = $this->db->query($query);
			$nbrows = $result->num_rows();
			$limit = $query." LIMIT ".$start.",".$end;
			$result = $this->db->query($limit);
				
			if($nbrows>0){
				foreach($result->result() as $row){
					$arr[] = $row;
				}
				$jsonresult = json_encode($arr);
				return '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
			} else {
				return '({"total":"0", "results":""})';
			}
		}
		
		function get_nosurat_byvendor($vendor,$nosurat) {
			if ($nosurat==""){
				return 0;
			}
			
			$query = "SELECT negosiasi_id, m_vendor_id, negosiasi_no
					FROM t_negosiasi
					WHERE m_vendor_id=".$vendor." AND negosiasi_no='".$nosurat."'";
			
			$result = $this->db->query($query);
			$nbrows = $result->num_rows();
			
			if($nbrows>0){
				return 0;
			} else {
				return 1;
			}
		}

		//function for get list record
		function t_negosiasi_list($filter,$start,$end){
			$sql = "SELECT nego_id, branch_id, branch_name, project_id, div_name, project_code, project_name, job_id, job_name, nego_date, nego_no, komp_id
					FROM t_nego JOIN t_negodet ON(t_negodet.t_nego_id = t_nego.nego_id)
					JOIN m_branch ON(m_branch.branch_id = t_nego.m_branch_id)
					JOIN t_project ON(t_project.project_id = t_nego.t_project_id)
					JOIN m_job ON(m_job.job_id = t_nego.m_job_id)
					LEFT JOIN t_komp ON(t_komp.komp_id = t_negodet.t_komp_id)
					LEFT JOIN t_rapk ON(t_komp.m_branch_id = t_rapk.m_branch_id) AND (t_komp.t_project_id = t_rapk.t_project_id) AND (t_komp.m_job_id = t_rapk.m_job_id)
					LEFT JOIN m_vendor ON(m_vendor.vendor_id = t_komp.m_vendor_id)
					LEFT JOIN m_div ON(m_div.div_id = t_rapk.m_div_id)
					";
			
			// For simple search
			if ($filter<>""){
				$sql .=preg_match("/WHERE/i",$sql)? " AND ":" WHERE ";
				$sql .= " (branch_name LIKE '%".addslashes($filter)."%' OR project_name LIKE '%".addslashes($filter)."%' )";
			}
			$sql .= "GROUP BY t_nego.m_branch_id, t_nego.t_project_id, t_nego.m_job_id";

			$result = $this->db->query($sql);
			$nbrows = $result->num_rows();
			$limit = $sql." LIMIT ".$start.", ".$end;
			$result = $this->db->query($limit);

			if($nbrows>0){
				foreach($result->result() as $row){
					$arr[] = $row;
				}
				$jsonresult = json_encode($arr);
				return '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
			} else {
				return '({"total":"0", "results":""})';
			}
		}
		
		function t_negosiasi_delete_temp(){	
				$sql = "DELETE FROM t_spk WHERE t_nego_id = 0";
				$this->db->query($sql);
				return 1;
		}

		//function for create new record
		function t_negosiasi_create($branch, $project, $job, $nego_date, $nego_harga, $nego_waktu, $nego_waktu_satuan, $nego_bayar, $nego_no,
				$array_t_komp_id, $array_negodet_harga, $array_negodet_keahlian, $array_negodet_metode, $array_negodet_peralatan, $array_negodet_k3l, 
				$array_negodet_waktu, $array_negodet_bayar, $array_negodet_lulus, $array_negodet_menang,$array_negodet_kp){
			//check apakah $branch, $project, dan $job telah pernah dimasukkan ke db.t_nego
			$sql = "SELECT nego_id FROM t_nego WHERE m_branch_id = ".$branch." AND t_project_id = ".$project." AND m_job_id = ".$job;
			$rs = $this->db->query($sql);
			if($rs->num_rows() > 0){
				$record = $rs->row();
				return $record->nego_id;
			}else{
				//insert master
				$data = array(
						"m_branch_id"=>$branch,
						"t_project_id"=>$project,
						"m_job_id"=>$job,
						"nego_date"=>$nego_date,
						"nego_rapktotal"=>$nego_harga,
						"nego_waktu"=>$nego_waktu,
						"nego_waktu_satuan"=>$nego_waktu_satuan,
						"nego_bayar"=>$nego_bayar,
						"nego_no"=>$nego_no
						);
				$this->db->insert('t_nego', $data);
				if($this->db->affected_rows()){
					$t_nego_id = $this->db->insert_id();
					//insert detail
					for($i = 0; $i < sizeof($array_t_komp_id); $i++){
						$t_komp_id = $array_t_komp_id[$i];
						$negodet_harga = $array_negodet_harga[$i];
						$negodet_keahlian = $array_negodet_keahlian[$i];
						$negodet_metode = $array_negodet_metode[$i];
						$negodet_peralatan = $array_negodet_peralatan[$i];
						$negodet_k3l = $array_negodet_k3l[$i];
						$negodet_waktu = $array_negodet_waktu[$i];
						$negodet_bayar = $array_negodet_bayar[$i];
						$negodet_lulus = $array_negodet_lulus[$i];
						$negodet_menang = $array_negodet_menang[$i];
						$negodet_kp = $array_negodet_kp[$i];
						
						$data_detail = array(
								"t_nego_id"=>$t_nego_id,
								"t_komp_id"=>$t_komp_id,
								"negodet_harga"=>$negodet_harga,
								"negodet_keahlian"=>$negodet_keahlian,
								"negodet_metode"=>$negodet_metode,
								"negodet_peralatan"=>$negodet_peralatan,
								"negodet_k3l"=>$negodet_k3l,
								"negodet_waktu"=>$negodet_waktu,
								"negodet_bayar"=>$negodet_bayar,
								"negodet_lulus"=>$negodet_lulus,
								"negodet_menang"=>$negodet_menang,
								"negodet_kp"=>$negodet_kp
								);
						$this->db->insert('t_negodet', $data_detail);
						
						$data_ba = array(
								"t_nego_id"=>$t_nego_id,
						);
						
						$this->db->where("t_nego_id",0);
						$this->db->update('t_spk', $data_ba);
					}
					
					return $t_nego_id;
				}else {
					return '0';
				}
			}
		}

		//function for update record
		function t_negosiasi_update($nego_id, $branch, $project, $job, $nego_date, $nego_harga, $nego_waktu, $nego_waktu_satuan, $nego_bayar, 
				$array_negodet_id, $array_t_komp_id, $array_negodet_harga, $array_negodet_keahlian, $array_negodet_metode, $array_negodet_peralatan, $array_negodet_k3l, 
				$array_negodet_waktu, $array_negodet_bayar, $array_negodet_lulus, $array_negodet_menang,$array_negodet_kp){
			if($nego_id > 0){
				$data = array(
						"nego_date"=>$nego_date,
						"nego_rapktotal"=>$nego_harga,
						"nego_waktu"=>$nego_waktu,
						"nego_waktu_satuan"=>$nego_waktu_satuan,
						"nego_bayar"=>$nego_bayar
				);
				
				if(is_numeric($branch)){
					$sql_branch = "SELECT branch_id FROM m_branch WHERE branch_id='".$branch."'";
					$rs_branch = $this->db->query($sql_branch);
					if($rs_branch->num_rows()){
						$row = $rs_branch->row();
						$data['m_branch_id'] = $row->branch_id;
					}
				}
				
				if(is_numeric($project)){
					$sql_project = "SELECT project_id FROM t_project WHERE project_id='".$project."'";
					$rs_project = $this->db->query($sql_project);
					if($rs_project->num_rows()){
						$row = $rs_project->row();
						$data['t_project_id'] = $row->project_id;
					}
				}
				
				if(is_numeric($job)){
					$sql_job = "SELECT job_id FROM m_job WHERE job_id='".$job."'";
					$rs_job = $this->db->query($sql_job);
					if($rs_job->num_rows()){
						$row = $rs_job->row();
						$data['m_job_id'] = $row->job_id;
					}
				}
				
				$this->db->where('nego_id', $nego_id);
				$this->db->update('t_nego', $data);
				
				//insert detail
				for($i = 0; $i < sizeof($array_t_komp_id); $i++){
					$negodet_id = $array_negodet_id[$i];
					$t_komp_id = $array_t_komp_id[$i];
					$negodet_harga = $array_negodet_harga[$i];
					$negodet_keahlian = $array_negodet_keahlian[$i];
					$negodet_metode = $array_negodet_metode[$i];
					$negodet_peralatan = $array_negodet_peralatan[$i];
					$negodet_k3l = $array_negodet_k3l[$i];
					$negodet_waktu = $array_negodet_waktu[$i];
					$negodet_bayar = $array_negodet_bayar[$i];
					$negodet_lulus = $array_negodet_lulus[$i];
					$negodet_menang = $array_negodet_menang[$i];
						$negodet_kp = $array_negodet_kp[$i];
					
					$data_detail = array(
							"t_nego_id"=>$nego_id,
							"t_komp_id"=>$t_komp_id,
							"negodet_harga"=>$negodet_harga,
							"negodet_keahlian"=>$negodet_keahlian,
							"negodet_metode"=>$negodet_metode,
							"negodet_peralatan"=>$negodet_peralatan,
							"negodet_k3l"=>$negodet_k3l,
							"negodet_waktu"=>$negodet_waktu,
							"negodet_bayar"=>$negodet_bayar,
							"negodet_lulus"=>$negodet_lulus,
							"negodet_menang"=>$negodet_menang,
							"negodet_kp"=>$negodet_kp
					);
					if($negodet_id > 0){
						$this->db->where('negodet_id', $negodet_id);
						$this->db->update('t_negodet', $data_detail);
					}
				}
				
				return $nego_id;
			}else{
				return '0';
			}
		}

		//fcuntion for delete record
		function t_negosiasi_delete($pkid){
			// You could do some checkups here and return '0' or other error consts.
			// Make a single query to delete all of the t_negosiasis at the same time :
			if(sizeof($pkid)<1){
				return '0';
			} else if (sizeof($pkid) == 1){
			
				$sql_check = "SELECT t_spk.t_nego_id  FROM t_spk WHERE t_nego_id = ".$pkid[0];
			//	$query = $this->db->query($sql);
				
				$query_check = $this->db->query($sql_check);
				if ($query_check->num_rows() == 0 ){
					$sql = "DELETE FROM t_project WHERE project_id = ".$pkid[0];
					$this->db->query($sql);
					return '1';
				
					$sql = "DELETE FROM t_nego WHERE nego_id = ".$pkid[0];
					$this->db->query($sql);
					
					$sql = "DELETE FROM t_negodet WHERE t_nego_id = ".$pkid[0];
					$this->db->query($sql);
					
					$sql = "select spk_id from t_spk where t_nego_id = ".$pkid[0];
					$query = $this->db->query($sql);
					if ($query->num_rows() > 0){
						$row = $query->row();
						$ba_id = $row->spk_id;
						
						$sql = "DELETE FROM t_spk WHERE t_nego_id = ".$pkid[0];
						$this->db->query($sql);
						$sql = "DELETE FROM t_spkdet WHERE t_spk_id = ".$ba_id;
						$this->db->query($sql);
					}
					
				
				}else{
					return '3';
				}
			} else {
			
				$sql = "SELECT t_spk.t_nego_id  FROM t_spk WHERE ";
				$query = $this->db->query($sql);
				
				for($i = 0; $i < sizeof($pkid); $i++){
					$sql_check = $sql_check . "t_nego_id= ".$pkid[$i];
					if($i<sizeof($pkid)-1){
						$sql_check = $sql_check . " OR ";
					}
				}
				
				if ($query->num_rows() == 0 ){
					
					
					$sql = "DELETE FROM t_nego WHERE ";
					for($i = 0; $i < sizeof($pkid); $i++){
						$sql = $sql . "nego_id= ".$pkid[$i];
						if($i<sizeof($pkid)-1){
							$sql = $sql . " OR ";
						}
					}
					
					$this->db->query($sql);
					
					$sql = "DELETE FROM t_negodet WHERE ";
					for($i = 0; $i < sizeof($pkid); $i++){
						$sql = $sql . "t_nego_id= ".$pkid[$i];
						if($i<sizeof($pkid)-1){
							$sql = $sql . " OR ";
						}
					}
					$this->db->query($sql);
					
					$sql = "DELETE FROM t_spk WHERE ";
					for($i = 0; $i < sizeof($pkid); $i++){
						$sql = $sql . "t_nego_id= ".$pkid[$i];
						if($i<sizeof($pkid)-1){
							$sql = $sql . " OR ";
						}
						
						$sub_sql = "select spk_id from t_spk where t_nego_id = ".$pkid[0];
						$sub_query = $this->db->query($sub_sql);
						
						if ($sub_query->num_rows() > 0){
							$row = $query->row();
							$ba_id = $row->spk_id;
							
							$sub_sql = "DELETE FROM t_spkdet WHERE t_spk_id = ".$ba_id;
							$this->db->query($sub_sql);
						}
					}
					$this->db->query($sql);
				
					return 1;
				}else{
					
					return 3;
				}
			}
		/*	if($this->db->affected_rows()>0)
				return '1';
			else
				return '0'; */
		}

		//function for advanced search record
		function t_negosiasi_search($negosiasi_id,
				 $negosiasi_flag,
				 $negosiasi_code,
				 $negosiasi_type,
				 $negosiasi_name,
				 $negosiasi_shortname,
				 $negosiasi_pm,
				 $negosiasi_semcm,
				 $negosiasi_value,
				 $negosiasi_start,
				 $negosiasi_p1,
				 $negosiasi_p2,
				 $negosiasi_created_date,
				 $negosiasi_created_by,
				 $negosiasi_updated_date,
				 $negosiasi_updated_by,
				 $negosiasi_updated_count,
				 $m_branch,$start,$end){
			//full query
			$sql = "SELECT negosiasi_id,
						   negosiasi_flag,
						   negosiasi_code,
						   negosiasi_type,
						   negosiasi_name,
						   negosiasi_shortname,
						   negosiasi_pm,
						   negosiasi_semcm,
						   negosiasi_value,
						   negosiasi_start,
						   negosiasi_p1,
						   negosiasi_p2,
						   negosiasi_created_date,
						   negosiasi_created_by,
						   negosiasi_updated_date,
						   negosiasi_updated_by,
						   negosiasi_updated_count,
						   m_branch_id
					FROM   t_negosiasi";

			if($negosiasi_id!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(negosiasi_id) LIKE '%".addslashes(strtolower($negosiasi_id))."%'";
			};
			if($negosiasi_flag!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(negosiasi_flag) LIKE '%".addslashes(strtolower($negosiasi_flag))."%'";
			};
			if($negosiasi_code!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(negosiasi_code) LIKE '%".addslashes(strtolower($negosiasi_code))."%'";
			};
			if($negosiasi_type!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(negosiasi_type) LIKE '%".addslashes(strtolower($negosiasi_type))."%'";
			};
			if($negosiasi_name!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(negosiasi_name) LIKE '%".addslashes(strtolower($negosiasi_name))."%'";
			};
			if($negosiasi_shortname!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(negosiasi_shortname) LIKE '%".addslashes(strtolower($negosiasi_shortname))."%'";
			};
			if($negosiasi_pm!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(negosiasi_pm) LIKE '%".addslashes(strtolower($negosiasi_pm))."%'";
			};
			if($negosiasi_semcm!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(negosiasi_semcm) LIKE '%".addslashes(strtolower($negosiasi_semcm))."%'";
			};
			if($negosiasi_value!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(negosiasi_value) LIKE '%".addslashes(strtolower($negosiasi_value))."%'";
			};
			if($negosiasi_start!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(negosiasi_start) LIKE '%".addslashes(strtolower($negosiasi_start))."%'";
			};
			if($negosiasi_p1!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(negosiasi_p1) LIKE '%".addslashes(strtolower($negosiasi_p1))."%'";
			};
			if($negosiasi_p2!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(negosiasi_p2) LIKE '%".addslashes(strtolower($negosiasi_p2))."%'";
			};
			if($negosiasi_created_date!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(negosiasi_created_date) LIKE '%".addslashes(strtolower($negosiasi_created_date))."%'";
			};
			if($negosiasi_created_by!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(negosiasi_created_by) LIKE '%".addslashes(strtolower($negosiasi_created_by))."%'";
			};
			if($negosiasi_updated_date!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(negosiasi_updated_date) LIKE '%".addslashes(strtolower($negosiasi_updated_date))."%'";
			};
			if($negosiasi_updated_by!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(negosiasi_updated_by) LIKE '%".addslashes(strtolower($negosiasi_updated_by))."%'";
			};
			if($negosiasi_updated_count!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(negosiasi_updated_count) LIKE '%".addslashes(strtolower($negosiasi_updated_count))."%'";
			};
			if($m_branch!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(m_branch_id) LIKE '%".addslashes(strtolower($m_branch))."%'";
			};

			$result = $this->db->query($sql);
			$nbrows = $result->num_rows();
			$limit = $sql." LIMIT ".$start.",".$end;
			$result = $this->db->query($limit);

			if($nbrows>0){
				foreach($result->result() as $row){
					$arr[] = $row;
				}
				$jsonresult = json_encode($arr);
				return '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
			} else {
				return '({"total":"0", "results":""})';
			}
		}

		//function for print record
		function t_negosiasi_print($negosiasi_id,
				 $negosiasi_flag,
				 $negosiasi_code,
				 $negosiasi_type,
				 $negosiasi_name,
				 $negosiasi_shortname,
				 $negosiasi_pm,
				 $negosiasi_semcm,
				 $negosiasi_value,
				 $negosiasi_start,
				 $negosiasi_p1,
				 $negosiasi_p2,
				 $negosiasi_created_date,
				 $negosiasi_created_by,
				 $negosiasi_updated_date,
				 $negosiasi_updated_by,
				 $negosiasi_updated_count,
				 $m_branch,$option,$filter){
			//full query
			$sql = "SELECT negosiasi_id,
						   negosiasi_flag,
						   negosiasi_code,
						   negosiasi_type,
						   negosiasi_name,
						   negosiasi_shortname,
						   negosiasi_pm,
						   negosiasi_semcm,
						   negosiasi_value,
						   negosiasi_start,
						   negosiasi_p1,
						   negosiasi_p2,
						   negosiasi_created_date,
						   negosiasi_created_by,
						   negosiasi_updated_date,
						   negosiasi_updated_by,
						   negosiasi_updated_count,
						   m_branch_id
				    FROM   t_negosiasi";

			if($option=='LIST'){
				$sql .=preg_match("/WHERE/i",$sql)? " AND ":" WHERE ";
				$sql .= " (lower(negosiasi_id) LIKE '%".addslashes(strtolower($filter))."%' OR lower(negosiasi_flag) LIKE '%".addslashes(strtolower($filter))."%' OR lower(negosiasi_code) LIKE '%".addslashes(strtolower($filter))."%' OR lower(negosiasi_type) LIKE '%".addslashes(strtolower($filter))."%' OR lower(negosiasi_name) LIKE '%".addslashes(strtolower($filter))."%' OR lower(negosiasi_shortname) LIKE '%".addslashes(strtolower($filter))."%' OR lower(negosiasi_pm) LIKE '%".addslashes(strtolower($filter))."%' OR lower(negosiasi_semcm) LIKE '%".addslashes(strtolower($filter))."%' OR lower(negosiasi_value) LIKE '%".addslashes(strtolower($filter))."%' OR lower(negosiasi_start) LIKE '%".addslashes(strtolower($filter))."%' OR lower(negosiasi_p1) LIKE '%".addslashes(strtolower($filter))."%' OR lower(negosiasi_p2) LIKE '%".addslashes(strtolower($filter))."%' OR lower(negosiasi_created_date) LIKE '%".addslashes(strtolower($filter))."%' OR lower(negosiasi_created_by) LIKE '%".addslashes(strtolower($filter))."%' OR lower(negosiasi_updated_date) LIKE '%".addslashes(strtolower($filter))."%' OR lower(negosiasi_updated_by) LIKE '%".addslashes(strtolower($filter))."%' OR lower(negosiasi_updated_count) LIKE '%".addslashes(strtolower($filter))."%' OR lower(m_branch_id) LIKE '%".addslashes(strtolower($filter))."%' )";
			} else if($option=='SEARCH'){
				if($negosiasi_id!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_id) LIKE '%".addslashes(strtolower($negosiasi_id))."%'";
				};
				if($negosiasi_flag!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_flag) LIKE '%".addslashes(strtolower($negosiasi_flag))."%'";
				};
				if($negosiasi_code!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_code) LIKE '%".addslashes(strtolower($negosiasi_code))."%'";
				};
				if($negosiasi_type!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_type) LIKE '%".addslashes(strtolower($negosiasi_type))."%'";
				};
				if($negosiasi_name!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_name) LIKE '%".addslashes(strtolower($negosiasi_name))."%'";
				};
				if($negosiasi_shortname!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_shortname) LIKE '%".addslashes(strtolower($negosiasi_shortname))."%'";
				};
				if($negosiasi_pm!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_pm) LIKE '%".addslashes(strtolower($negosiasi_pm))."%'";
				};
				if($negosiasi_semcm!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_semcm) LIKE '%".addslashes(strtolower($negosiasi_semcm))."%'";
				};
				if($negosiasi_value!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_value) LIKE '%".addslashes(strtolower($negosiasi_value))."%'";
				};
				if($negosiasi_start!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_start) LIKE '%".addslashes(strtolower($negosiasi_start))."%'";
				};
				if($negosiasi_p1!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_p1) LIKE '%".addslashes(strtolower($negosiasi_p1))."%'";
				};
				if($negosiasi_p2!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_p2) LIKE '%".addslashes(strtolower($negosiasi_p2))."%'";
				};
				if($negosiasi_created_date!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_created_date) LIKE '%".addslashes(strtolower($negosiasi_created_date))."%'";
				};
				if($negosiasi_created_by!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_created_by) LIKE '%".addslashes(strtolower($negosiasi_created_by))."%'";
				};
				if($negosiasi_updated_date!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_updated_date) LIKE '%".addslashes(strtolower($negosiasi_updated_date))."%'";
				};
				if($negosiasi_updated_by!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_updated_by) LIKE '%".addslashes(strtolower($negosiasi_updated_by))."%'";
				};
				if($negosiasi_updated_count!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_updated_count) LIKE '%".addslashes(strtolower($negosiasi_updated_count))."%'";
				};
				if($m_branch!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(m_branch_id) LIKE '%".addslashes(strtolower($m_branch))."%'";
				};
			}
			$query = $this->db->query($sql);
			return $query->result();
		}

		//function  for export to excel
		function t_negosiasi_export_excel($negosiasi_id ,$negosiasi_flag ,$negosiasi_code ,$negosiasi_type ,$negosiasi_name ,$negosiasi_shortname ,$negosiasi_pm ,$negosiasi_semcm ,$negosiasi_value ,$negosiasi_start ,$negosiasi_p1 ,$negosiasi_p2 ,$negosiasi_created_date ,$negosiasi_created_by ,$negosiasi_updated_date ,$negosiasi_updated_by ,$negosiasi_updated_count ,$m_branch ,$option,$filter){
			//full query
			$sql = "SELECT negosiasi_id,
						   negosiasi_flag,
						   negosiasi_code,
						   negosiasi_type,
						   negosiasi_name,
						   negosiasi_shortname,
						   negosiasi_pm,
						   negosiasi_semcm,
						   negosiasi_value,
						   negosiasi_start,
						   negosiasi_p1,
						   negosiasi_p2,
						   negosiasi_created_date,
						   negosiasi_created_by,
						   negosiasi_updated_date,
						   negosiasi_updated_by,
						   negosiasi_updated_count,
						   m_branch_id				    FROM   t_negosiasi";

			if($option=='LIST'){
				$sql .=preg_match("/WHERE/i",$sql)? " AND ":" WHERE ";
				$sql .= "(lower(negosiasi_id) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(negosiasi_flag) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(negosiasi_code) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(negosiasi_type) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(negosiasi_name) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(negosiasi_shortname) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(negosiasi_pm) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(negosiasi_semcm) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(negosiasi_value) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(negosiasi_start) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(negosiasi_p1) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(negosiasi_p2) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(negosiasi_created_date) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(negosiasi_created_by) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(negosiasi_updated_date) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(negosiasi_updated_by) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(negosiasi_updated_count) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(m_branch_id) LIKE '%".addslashes(strtolower($filter))."%' )";
			} else if($option=='SEARCH'){
				if($negosiasi_id!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_id) LIKE '%".addslashes(strtolower($negosiasi_id))."%'";
				};
				if($negosiasi_flag!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_flag) LIKE '%".addslashes(strtolower($negosiasi_flag))."%'";
				};
				if($negosiasi_code!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_code) LIKE '%".addslashes(strtolower($negosiasi_code))."%'";
				};
				if($negosiasi_type!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_type) LIKE '%".addslashes(strtolower($negosiasi_type))."%'";
				};
				if($negosiasi_name!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_name) LIKE '%".addslashes(strtolower($negosiasi_name))."%'";
				};
				if($negosiasi_shortname!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_shortname) LIKE '%".addslashes(strtolower($negosiasi_shortname))."%'";
				};
				if($negosiasi_pm!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_pm) LIKE '%".addslashes(strtolower($negosiasi_pm))."%'";
				};
				if($negosiasi_semcm!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_semcm) LIKE '%".addslashes(strtolower($negosiasi_semcm))."%'";
				};
				if($negosiasi_value!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_value) LIKE '%".addslashes(strtolower($negosiasi_value))."%'";
				};
				if($negosiasi_start!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_start) LIKE '%".addslashes(strtolower($negosiasi_start))."%'";
				};
				if($negosiasi_p1!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_p1) LIKE '%".addslashes(strtolower($negosiasi_p1))."%'";
				};
				if($negosiasi_p2!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_p2) LIKE '%".addslashes(strtolower($negosiasi_p2))."%'";
				};
				if($negosiasi_created_date!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_created_date) LIKE '%".addslashes(strtolower($negosiasi_created_date))."%'";
				};
				if($negosiasi_created_by!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_created_by) LIKE '%".addslashes(strtolower($negosiasi_created_by))."%'";
				};
				if($negosiasi_updated_date!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_updated_date) LIKE '%".addslashes(strtolower($negosiasi_updated_date))."%'";
				};
				if($negosiasi_updated_by!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_updated_by) LIKE '%".addslashes(strtolower($negosiasi_updated_by))."%'";
				};
				if($negosiasi_updated_count!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(negosiasi_updated_count) LIKE '%".addslashes(strtolower($negosiasi_updated_count))."%'";
				};
				if($m_branch!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(m_branch_id) LIKE '%".addslashes(strtolower($m_branch))."%'";
				};
			}
			$query = $this->db->query($sql);
			return $sql;
		}
		
		function negodet_byfilter($branch, $project, $job){
			$query = "SELECT t_negodet.negodet_id, t_nego.nego_rapktotal, t_nego.nego_waktu, t_nego.nego_waktu_satuan, t_nego.nego_bayar,
						m_vendor.vendor_id, m_vendor.vendor_name, t_komp.komp_id, t_komp.komp_date, t_komp.komp_no, t_negodet.negodet_id, t_negodet.negodet_harga AS komp_total, 
						t_negodet.negodet_keahlian, t_negodet.negodet_metode, t_negodet.negodet_peralatan, t_negodet.negodet_k3l, t_negodet.negodet_waktu,
						t_negodet.negodet_bayar, t_negodet.negodet_lulus, t_negodet.negodet_menang, t_negodet.negodet_kp
					FROM t_nego JOIN t_negodet ON(t_negodet.t_nego_id = t_nego.nego_id)
					LEFT JOIN t_komp ON(t_komp.komp_id = t_negodet.t_komp_id)
					LEFT JOIN m_vendor ON(m_vendor.vendor_id = t_komp.m_vendor_id)
					WHERE t_nego.m_branch_id = ".$branch." AND t_nego.t_project_id = ".$project." AND t_nego.m_job_id = ".$job;
			$this->firephp->log($query);
			$result = $this->db->query($query);
			$nbrows = $result->num_rows();
				
			if($nbrows>0){
				foreach($result->result() as $row){
					$arr[] = $row;
				}
				$jsonresult = json_encode($arr);
				return '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
			} else {
				return '({"total":"0", "results":""})';
			}
		}
		
		function badet_atkompdet_list($t_komp_id){
			$query = "SELECT kompdet_id, jobdet_id, jobdet_name, kompdet_vol, unit_id, unit_name
					FROM t_kompdet JOIN m_jobdet ON(m_jobdet.jobdet_id = t_kompdet.m_jobdet_id)
					LEFT JOIN m_unit ON(m_unit.unit_id = t_kompdet.kompdet_unit) 
					WHERE t_komp_id=".$t_komp_id;
				
			$result = $this->db->query($query);
			$nbrows = $result->num_rows();
			
			if($nbrows>0){
				foreach($result->result() as $row){
					$arr[] = $row;
				}
				$jsonresult = json_encode($arr);
				return '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
			} else {
				return '({"total":"0", "results":""})';
			}
		}
		
		function dataspkdetail($data_submit){
			extract($data_submit);
		/* 	$sql = "SELECT kompdet_id,jobdet_id, jobdet_name, kompdet_vol , unit_name, kompdet_price FROM t_kompdet
					LEFT JOIN m_jobdet ON m_jobdet_id = jobdet_id
					LEFT JOIN m_unit ON kompdet_unit = unit_id
					 WHERE t_komp_id = ".$t_komp_id; */
			
			$query = "SELECT  m_div_id, div_name FROM t_komp LEFT JOIN t_rapk ON 
			( t_komp.m_branch_id =  t_rapk.m_branch_id AND t_komp.m_job_id =  t_rapk.m_job_id  AND t_komp.t_project_id =  t_rapk.t_project_id )
				LEFT JOIN m_div ON m_div_id = div_id 			WHERE komp_id  = ".$t_komp_id;
			$row = $this->db->query($query);
			$divisi = $row->row('div_name');		 
					 		
			if ( $divisi != '503. Sub Kontraktor'){
			$sql = "SELECT unit_name,kompdet_id, kompdet_unit, kompdet_vol, kompdet_price, m_jobdet_id, jobdet_name, 0 AS depth 
					FROM t_komp JOIN t_kompdet ON(t_komp.komp_id = t_kompdet.t_komp_id)
					LEFT JOIN m_unit ON(m_unit.unit_id = t_kompdet.kompdet_unit)
					LEFT JOIN m_jobdet ON(t_kompdet.m_jobdet_id = m_jobdet.jobdet_id)
					WHERE komp_id = ".$t_komp_id;
			}else{
			$sql = "
				SELECT unit_name , node.kompdet_unit , node.kompdet_vol , node.kompdet_price , 
				node.kompdet_id AS kompdet_id, jobdet_id, IFNULL(CONCAT(REPEAT('&nbsp;&nbsp;', COUNT(parent.kompdet_id) - 1), jobdet_name),node.kompdet_uraian) AS jobdet_name,
							(COUNT(parent.kompdet_id) - 1) AS depth 
				FROM t_kompdet as node
				Join
				t_kompdet as parent
				ON(node.kompdet_lft BETWEEN parent.kompdet_lft AND parent.kompdet_rgt)
				left join m_jobdet ON m_jobdet.jobdet_id = node.m_jobdet_id
				
					LEFT JOIN m_unit ON(m_unit.unit_id = node.kompdet_unit)
				where node.t_komp_id = ".$t_komp_id."
				GROUP BY node.kompdet_id
				ORDER BY node.kompdet_lft
					"; 
			}
					 
			$query = $this->db->query($sql);
			$result = $query->result();
			return $result;
		}
		
		function dataspk($data_submit){
			extract($data_submit);
			$sql = "SELECT branch_name, project_name, vendor_name, vendor_address, vendor_phone, vendor_fax, vendor_dir, vendor_dirpos, 
				job_name, t_project.project_pm as pm, 
					m_setup.vvalue1 as proc_name, m_setup.vvalue2 as proc_pos, 
					setup_kdvo.vvalue1 as kdvo_name, setup_kdvo.vvalue2 as kdvo_pos   FROM t_komp 
					LEFT JOIN m_branch ON m_branch_id =  branch_id
					LEFT JOIN t_project  ON t_project_id =  project_id
					LEFT JOIN m_vendor  ON m_vendor_id =  vendor_id
					LEFT JOIN m_job  ON m_job_id =  job_id
					LEFT JOIN t_rapk  ON ( t_komp.m_branch_id =  t_rapk.m_branch_id AND t_komp.m_job_id =  t_rapk.m_job_id  AND t_komp.t_project_id =  t_rapk.t_project_id )
					LEFT JOIN m_setup ON m_setup.param1 = 'PROCM'
					LEFT JOIN m_setup as setup_kdvo ON setup_kdvo.param1 = 'KDVO'
					WHERE t_komp.komp_id = ".$t_komp_id ;
			$query = $this->db->query($sql);
			$result = $query->row();
			return $result;
		}
		
		function ba_cu($t_nego_id, $t_komp_id, $ba_date, $ba_vendor1, $ba_vendor2, $ba_vendor3, $ba_pp1, $ba_pp2, 
				$array_t_kompdet_id, $array_t_badet_vol, $array_badet_unit){
			// check data lama
			$data_where = array(
					"t_nego_id"=>$t_nego_id,
					"t_komp_id"=>$t_komp_id
					);
			$this->db->where($data_where);
			$query = $this->db->get('t_ba');
			$row = $query->num_rows();
			
			$data = array(
					"t_nego_id"=>$t_nego_id,
					"t_komp_id"=>$t_komp_id,
					"ba_date"=>$ba_date,
					"ba_vendor1"=>$ba_vendor1,
					"ba_vendor2"=>$ba_vendor2,
					"ba_vendor3"=>$ba_vendor3,
					"ba_pp1"=>$ba_pp1,
					"ba_pp2"=>$ba_pp2,
			);
			
			if ( $row != 0 ){
				$this->db->where($data_where);
				$this->db->update('t_ba', $data);
				$no_error = 1;
			}else{
				$this->db->insert('t_ba', $data);
				$no_error = $this->db->affected_rows();
			}
			
			if($no_error){
				$ba_id = $this->db->insert_id();
				// delete semua detail yang ba_id = $ba_id 
				$query = "DELETE FROM t_badet WHERE t_ba_id = ".$ba_id ;
				$result = $this->db->query($query);
				
				for($i = 0; $i < sizeof($array_t_kompdet_id); $i++){
					$t_kompdet_id = $array_t_kompdet_id[$i];
					$t_badet_vol = $array_t_badet_vol[$i];
					$badet_unit = $array_badet_unit[$i];
			
					$data_detail = array(
							"t_ba_id"=>$ba_id,
							"t_kompdet_id"=>$t_kompdet_id,
							"badet_vol"=>$t_badet_vol,
							"badet_unit"=>$badet_unit
					);
					$this->db->insert('t_badet', $data_detail);
				}
			
				return '1';
			}else{
				return '0';
			}
		}
		
				
		function spk_cu($t_nego_id, $t_komp_id, $spk_no,$spk_date,
				$array_t_kompdet_id, $array_spkdet_spec,$spek_field,$array_t_spkdet_id,$html){
			// check data lama
			$data_where = array(
				"t_nego_id"=>$t_nego_id,
				"t_komp_id"=>$t_komp_id
			);
			$this->db->where($data_where);
			$query = $this->db->get('t_spk');
			$row = $query->num_rows();
			
			$data = array(
					"t_nego_id"=>$t_nego_id,
					"t_komp_id"=>$t_komp_id,
					"spk_no"=>$spk_no,
					"spk_date"=>$spk_date,
					"spk_spec"=>$spek_field,
					"spk_cetak"=>$html
			);
			
			if ( $row != 0 ){
				$this->db->where($data_where);
				$this->db->update('t_spk', $data);
				$no_error = 1;
				$spk_id = $query->row('spk_id');
			}else{
				$this->db->insert('t_spk', $data);
				$no_error = $this->db->affected_rows();
				$spk_id = $this->db->insert_id();
			}
			
			if($no_error){
				// delete semua detail yang ba_id = $ba_id 
				$query = "DELETE FROM t_spkdet WHERE t_spk_id = ".$spk_id ;
				$result = $this->db->query($query);
				
				
					for($i = 0; $i < sizeof($array_t_kompdet_id); $i++){
					$spkdet_id = $array_t_spkdet_id[$i];
					$t_kompdet_id = $array_t_kompdet_id[$i];
					$spkdet_spec = $array_spkdet_spec[$i];
					
					$query = "DELETE FROM t_spkdet WHERE spkdet_id = ".$spkdet_id ;
					$result = $this->db->query($query);
			
					$data_detail = array(
							"spkdet_id"=>$spkdet_id,
							"t_spk_id"=>$spk_id,
							"t_kompdet_id"=>$t_kompdet_id,
							"spkdet_spec"=>$spkdet_spec,
					);
					
					$this->db->insert('t_spkdet', $data_detail);
				}
			
				return $spk_id;
			}else{
				return '0';
			}
		}
		
		function badet_list($t_nego_id, $t_komp_id) {
/* 			$sql = "SELECT badet_id, t_kompdet_id, badet_unit, badet_vol, unit_name, jobdet_id
					FROM t_ba JOIN t_badet ON(t_badet.t_ba_id = t_ba.ba_id)
					LEFT JOIN m_unit ON(m_unit.unit_id = t_badet.badet_unit)
					LEFT JOIN t_kompdet ON(t_kompdet.kompdet_id = t_badet.t_kompdet_id)
					LEFT JOIN m_jobdet ON(t_kompdet.m_jobdet_id = m_jobdet.jobdet_id)
					WHERE t_ba.t_nego_id = ".$t_nego_id." AND t_ba.t_komp_id = ".$t_komp_id; */
			
		/*	$sql = "SELECT unit_name,kompdet_id, kompdet_unit, kompdet_vol, kompdet_price, m_jobdet_id, jobdet_name
					FROM t_komp JOIN t_kompdet ON(t_komp.komp_id = t_kompdet.t_komp_id)
					LEFT JOIN m_unit ON(m_unit.unit_id = t_kompdet.kompdet_unit)
					LEFT JOIN m_jobdet ON(t_kompdet.m_jobdet_id = m_jobdet.jobdet_id)
					WHERE komp_id = ".$t_komp_id;
			*/
			$query = "SELECT  m_div_id, div_name FROM t_komp
			LEFT JOIN t_rapk ON		
			t_komp.t_project_id = t_rapk.t_project_id AND
			t_komp.m_branch_id = t_rapk.m_branch_id AND
			t_komp.m_job_id = t_rapk.m_job_id 
			LEFT JOIN m_div ON 
			m_div_id = div_id
			WHERE komp_id  = ".$t_komp_id;
			$row = $this->db->query($query);
			$divisi = $row->row('div_name');
				
			if ( $divisi != '503. Sub Kontraktor'){
			$sql = "SELECT unit_name,kompdet_id, kompdet_unit, kompdet_vol,spkdet_spec, kompdet_price, m_jobdet_id, jobdet_name, spkdet_id
					FROM t_komp JOIN t_kompdet ON(t_komp.komp_id = t_kompdet.t_komp_id)
					LEFT JOIN t_spkdet ON(t_spkdet.t_kompdet_id = t_kompdet.kompdet_id)
					LEFT JOIN m_unit ON(m_unit.unit_id = t_kompdet.kompdet_unit)
					LEFT JOIN m_jobdet ON(t_kompdet.m_jobdet_id = m_jobdet.jobdet_id)
					WHERE komp_id = ".$t_komp_id;
			}else{
			$sql = "
				SELECT spkdet_id, unit_name , node.kompdet_unit , node.kompdet_vol , node.kompdet_price , spkdet_spec,
				node.kompdet_id AS kompdet_id, jobdet_id, IFNULL(CONCAT(REPEAT('--', COUNT(parent.kompdet_id) - 1), jobdet_name),node.kompdet_uraian) AS jobdet_name,
							(COUNT(parent.kompdet_id) - 1) AS depth 
				FROM t_kompdet as node
				Join
				t_kompdet as parent
				ON(node.kompdet_lft BETWEEN parent.kompdet_lft AND parent.kompdet_rgt)
				left join m_jobdet ON m_jobdet.jobdet_id = node.m_jobdet_id
				LEFT JOIN t_spkdet ON(t_spkdet.t_kompdet_id = node.kompdet_id)
				LEFT JOIN m_unit ON(m_unit.unit_id = node.kompdet_unit)
				where node.t_komp_id = ".$t_komp_id."
				GROUP BY node.kompdet_id
				ORDER BY node.kompdet_lft
					"; 
			}
			
			$result = $this->db->query($sql);
			$nbrows = $result->num_rows();
		
			if($nbrows>0){
				foreach($result->result() as $row){
					$arr[] = $row;
				}
				$jsonresult = json_encode($arr);
				return '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
			} else {
				return '({"total":"0", "results":""})';
			}
		}
		
		function ba_list($t_nego_id, $t_komp_id) {
			$sql = "SELECT ba_id,ba_date,ba_vendor1,ba_vendor2,ba_vendor3,ba_pp1,ba_pp2
					FROM t_ba
					WHERE t_ba.t_nego_id = ".$t_nego_id." AND t_komp_id = ".$t_komp_id;
				
			$result = $this->db->query($sql);
			$nbrows = $result->num_rows();
		
			if($nbrows>0){
				foreach($result->result() as $row){
					$arr[] = $row;
				}
				$jsonresult = json_encode($arr);
				return '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
			} else {
				return '({"total":"0", "results":""})';
			}
		}
		
		function spk_list($t_nego_id, $t_komp_id) {
			$sql = "SELECT spk_id, spk_no, spk_date, spk_spec, spk_cetak
					FROM t_spk
					WHERE t_spk.t_nego_id = ".$t_nego_id." AND t_komp_id = ".$t_komp_id;
				
			$result = $this->db->query($sql);
			$nbrows = $result->num_rows();
		
			if($nbrows>0){
				foreach($result->result() as $row){
					$arr[] = $row;
				}
				$jsonresult = json_encode($arr);
				return '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
			} else {
				return '({"total":"0", "results":""})';
			}
		}
		
		function get_single_negodata($nego_id){
			$kuery = "SELECT * FROM t_nego
			LEFT JOIN m_job
			ON m_job_id = job_id
			LEFT JOIN m_branch
			ON m_branch_id = branch_id
			LEFT JOIN t_project
			ON t_project_id = project_id 
			LEFT JOIN m_dvo
			ON m_branch.m_dvo_id = dvo_id
			WHERE nego_id = '".$nego_id."' LIMIT 0,1";
			$query = $this->db->query($kuery);
			if($query->num_rows() > 0){
				$result = $query->result();
				return $result;
			}
		}
		function get_negodet_databynegoid($nego_id){
			$kuery = "SELECT t_nego.nego_rapktotal, t_nego.nego_waktu, t_nego.nego_waktu_satuan, t_nego.nego_bayar, payment_name, 
						m_vendor.vendor_id, m_vendor.vendor_name, t_komp.komp_date, t_komp.komp_no, t_negodet.negodet_id, t_negodet.negodet_harga AS komp_total, 
						t_negodet.negodet_keahlian, t_negodet.negodet_metode, t_negodet.negodet_peralatan, t_negodet.negodet_k3l, t_negodet.negodet_waktu,
						t_negodet.negodet_bayar, t_negodet.negodet_lulus, t_negodet.negodet_menang
					FROM t_nego JOIN t_negodet ON(t_negodet.t_nego_id = t_nego.nego_id)
					LEFT JOIN t_komp ON(t_komp.komp_id = t_negodet.t_komp_id)
					LEFT JOIN m_payment ON(payment_id = t_nego.nego_bayar)
					LEFT JOIN m_vendor ON(m_vendor.vendor_id = t_komp.m_vendor_id)
					WHERE t_nego_id = '".$nego_id."'";
			$query = $this->db->query($kuery);
			if($query->num_rows() > 0){
				$result = $query->result();
				return $result;
			}
		}
		function get_negomenang_databynegoid($nego_id){
			$kuery = "SELECT m_vendor.vendor_name, t_negodet.negodet_harga AS komp_total, div_name
					FROM t_nego JOIN t_negodet ON(t_negodet.t_nego_id = t_nego.nego_id)
					LEFT JOIN t_komp ON(t_komp.komp_id = t_negodet.t_komp_id)
					LEFT JOIN m_vendor ON(m_vendor.vendor_id = t_komp.m_vendor_id)
					LEFT JOIN t_rapk ON(t_nego.m_branch_id = t_rapk.m_branch_id) AND (t_nego.t_project_id = t_rapk.t_project_id) AND (t_nego.m_job_id = t_rapk.m_job_id)
					LEFT JOIN m_div ON(m_div.div_id = t_rapk.m_div_id)
					WHERE t_nego_id = '".$nego_id."' AND t_negodet.negodet_menang = 'Y'";
			$query = $this->db->query($kuery);
			if($query->num_rows() > 0){
				$result = $query->result();
				return $result;
			}
		}
		function get_negono($year){
			$kuery = "select substring(nego_no, -3) as last_negono from t_nego where substring(nego_no,14,4) = '" . $year . "' ORDER BY nego_id DESC limit 0,1";
			$query = $this->db->query($kuery);
			$num_rows = $query->num_rows();
			if($num_rows > 0){
				$row = $query->row();
				$new_no = ltrim($row->last_negono,'0') + 1;
				if(strlen($new_no) == 1){
					$nego_no = "7.12/5.2/QSH-" . $year . "/PMT/AG/W/00";
				}else if(strlen($new_no) == 2){
					$nego_no = "7.12/5.2/QSH-" . $year . "/PMT/AG/W/0";
				}else{
					$nego_no = "7.12/5.2/QSH-" . $year . "/PMT/AG/W/";
				}
				$nego_no .= $new_no;
				return $nego_no;
			}else{
				$nego_no = "7.12/5.2/QSH-" . $year . "/PMT/AG/W/001";
				return $nego_no;
			}
		}
		
		function spkUpdatePrint($spk_id,$value){
			$data = array(
				'spk_cetak' => $value
			);
			$this->db->where('spk_id',$spk_id);
			$this->db->update('t_spk',$data);
		}
		
		function getSpkCetak($spk_id){
			$this->db->where('spk_id',$spk_id);
			$query = $this->db->get('t_spk');
			return $query->row('spk_cetak');
		}
}
?>